-- =====================================================
-- Author:        Paul Bradshaw
-- Create date: 12-Nov-2006
-- Description:    Compares two version strings
--              Returns -1 if @ver1 < @ver2
--              Returns  0 if @ver1 = @ver2
--              Returns  1 if @ver1 > @ver2
-- =====================================================
CREATE FUNCTION [dbo].[fn_asi_CompareVersions](@ver1 nvarchar(25), @ver2 nvarchar(25))
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result int
    DECLARE @diff int

    DECLARE @DiffTable TABLE (ID int, Diff int)

    INSERT INTO @DiffTable(ID, Diff) SELECT TOP 1 ID, Diff FROM
    (SELECT v1.ElementID as ID, Sign(CAST(v1.Element AS int) - CAST(v2.Element AS int)) as Diff
        FROM dbo.asi_SplitString(@ver1, '.') v1 inner join
             dbo.asi_SplitString(@ver2, '.') v2 on v1.ElementID = v2.ElementID
     ) AS StrDiff
    WHERE Diff <> 0

    IF @@ROWCOUNT = 0
        SET @result = 0
    ELSE
        SELECT @result = Diff from @DiffTable

    -- Return the result of the function
    RETURN @result

END

GO
GRANT EXECUTE ON  [dbo].[fn_asi_CompareVersions] TO [IMIS]
GO